package com.ztesoft.sca.dao;

import com.ztesoft.sca.model.user.ImOrganizationPo;
import com.ztesoft.sca.model.user.ImRolePo;
import com.ztesoft.sca.model.user.ImUserPo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

@Component
public class ImOrgDaoImpl implements ImOrgDao{
    private JdbcTemplate jdbcTemplate;

    @Autowired
    public ImOrgDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    @Override
    public List<ImOrganizationPo> listImOrg(ImOrganizationPo imOrg, int from, int size) throws Exception {
    	StringBuffer sqlBuf = new StringBuffer();
    	List qryList=new ArrayList();
    	sqlBuf.append(" SELECT * FROM (SELECT ROWNUM rn, org_id, area_id, org_code, org_name, parent_id, org_code_path, org_name_path, org_alias ")
        	  .append(" FROM (SELECT org_id, area_id, org_code, org_name, parent_id, org_code_path, org_name_path, org_alias FROM IM_ORGANIZATION WHERE 1=1 ");
    	  
    	  if(imOrg.getOrgId()!=null){
	  		  sqlBuf.append(" AND org_id=? ");   
	  		  qryList.add(imOrg.getOrgId());
	  	  }
    	  
    	  if(imOrg.getAreaId()!=null){
	  		  sqlBuf.append(" AND area_id=? ");   
	  		  qryList.add(imOrg.getAreaId());
	  	  }
    	  
    	  
	  	  if(imOrg.getOrgCode()!=null && !"".equals(imOrg.getOrgCode())){
	  		  sqlBuf.append(" AND org_code=? ");   
	  		  qryList.add(imOrg.getOrgCode());
	  	  }
	  	  if(imOrg.getOrgName()!=null && !"".equals(imOrg.getOrgName())){
	  		  sqlBuf.append(" AND org_name like ? ");
	  		  qryList.add("%"+imOrg.getOrgName()+"%");
	  	  }
	  	  
	  	  if(imOrg.getParentId()!=null){
	  		  sqlBuf.append(" AND parent_id=? ");   
	  		  qryList.add(imOrg.getParentId());
	  	  }
	  	  
	  	  if(imOrg.getOrgAlias()!=null && !"".equals(imOrg.getOrgAlias())){
	  		  sqlBuf.append(" AND org_alias=? ");
	  		  qryList.add(imOrg.getOrgAlias());
	  	  }
    	
	  	sqlBuf.append(" ) WHERE ROWNUM<=? ORDER BY org_id asc) t2 WHERE T2.rn >=? ");
    	
        if (from < 0) {
            from = Integer.MAX_VALUE; 
        }
        
        qryList.add(size);
        qryList.add(from);
        Object[] param =qryList.toArray(); 
        List<ImOrganizationPo> list = this.jdbcTemplate.query(sqlBuf.toString(),param,BeanPropertyRowMapper.newInstance(ImOrganizationPo.class));
        return list;
    }





	@Override
    public List<ImRolePo> listImOrgRole(ImOrganizationPo imOrg, int from, int size) throws Exception {
    	StringBuffer sqlBuf = new StringBuffer();
    	List qryList=new ArrayList();
    	sqlBuf.append(" SELECT * FROM (SELECT ROWNUM rn, ROLE_ID, ROLE_CODE, ROLE_NAME, COMMENTS ")
        	  .append(" FROM (SELECT C.ROLE_ID, C.ROLE_CODE, C.ROLE_NAME, C.COMMENTS ")
        	  .append(" FROM IM_ORGANIZATION A JOIN IM_ORG_ROLE_RELA B ON A.ORG_ID=B.ORG_ID JOIN IM_ROLE C ON C.ROLE_ID=B.ROLE_ID WHERE 1=1  ");
    	  
    	  if(imOrg.getOrgId()!=null){
	  		  sqlBuf.append(" AND A.org_id=? ");   
	  		  qryList.add(imOrg.getOrgId());
	  	  }
    	  
    	  if(imOrg.getAreaId()!=null){
	  		  sqlBuf.append(" AND A.area_id=? ");   
	  		  qryList.add(imOrg.getAreaId());
	  	  }
    	  
    	  
	  	  if(imOrg.getOrgCode()!=null && !"".equals(imOrg.getOrgCode())){
	  		  sqlBuf.append(" AND A.org_code=? ");   
	  		  qryList.add(imOrg.getOrgCode());
	  	  }
	  	  if(imOrg.getOrgName()!=null && !"".equals(imOrg.getOrgName())){
	  		  sqlBuf.append(" AND A.org_name like ? ");
	  		  qryList.add("%"+imOrg.getOrgName()+"%");
	  	  }
	  	  
	  	  if(imOrg.getParentId()!=null){
	  		  sqlBuf.append(" AND A.parent_id=? ");   
	  		  qryList.add(imOrg.getParentId());
	  	  }
	  	  
	  	  if(imOrg.getOrgAlias()!=null && !"".equals(imOrg.getOrgAlias())){
	  		  sqlBuf.append(" AND A.org_alias=? ");
	  		  qryList.add(imOrg.getOrgAlias());
	  	  }
    	
	  	sqlBuf.append(" ) WHERE ROWNUM<=? ORDER BY C.ROLE_ID asc) t2 WHERE T2.rn >=? ");
    	
        if (from < 0) {
            from = Integer.MAX_VALUE; 
        }
        
        qryList.add(size);
        qryList.add(from);
        Object[] param =qryList.toArray(); 
        List<ImRolePo> list = this.jdbcTemplate.query(sqlBuf.toString(),param,BeanPropertyRowMapper.newInstance(ImRolePo.class));
        return list;
    }

	@Override
    public List<ImUserPo> listImOrgUser(ImOrganizationPo imOrg, int from, int size) throws Exception {
    	StringBuffer sqlBuf = new StringBuffer();
    	List qryList=new ArrayList();
    	sqlBuf.append(" SELECT * FROM (SELECT ROWNUM rn, USER_ID, PASSWD, USER_NAME, NICK_NAME, SIGN, AVATAR, MOBILE_TEL, HOME_TEL, EMAIL, REGISTER_TIME  ")
        	  .append(" FROM (SELECT E.USER_ID, E.PASSWD, E.USER_NAME, E.NICK_NAME, E.SIGN, E.AVATAR, E.MOBILE_TEL, E.HOME_TEL, E.EMAIL, E.REGISTER_TIME  ")
        	  .append(" FROM IM_ORGANIZATION A JOIN IM_ORG_ROLE_RELA B ON A.ORG_ID=B.ORG_ID JOIN IM_ROLE C ON C.ROLE_ID=B.ROLE_ID   ")
        	  .append(" JOIN IM_ROLE_USER_RELA D ON D.ROLE_ID=C.ROLE_ID JOIN IM_USER E ON D.USER_ID=E.USER_ID WHERE 1=1");
    	  
    	  if(imOrg.getOrgId()!=null){
	  		  sqlBuf.append(" AND A.org_id=? ");   
	  		  qryList.add(imOrg.getOrgId());
	  	  }
    	  
    	  if(imOrg.getAreaId()!=null){
	  		  sqlBuf.append(" AND A.area_id=? ");   
	  		  qryList.add(imOrg.getAreaId());
	  	  }
    	  
    	  
	  	  if(imOrg.getOrgCode()!=null && !"".equals(imOrg.getOrgCode())){
	  		  sqlBuf.append(" AND A.org_code=? ");   
	  		  qryList.add(imOrg.getOrgCode());
	  	  }
	  	  if(imOrg.getOrgName()!=null && !"".equals(imOrg.getOrgName())){
	  		  sqlBuf.append(" AND A.org_name like ? ");
	  		  qryList.add("%"+imOrg.getOrgName()+"%");
	  	  }
	  	  
	  	  if(imOrg.getParentId()!=null){
	  		  sqlBuf.append(" AND A.parent_id=? ");   
	  		  qryList.add(imOrg.getParentId());
	  	  }
	  	  
	  	  if(imOrg.getOrgAlias()!=null && !"".equals(imOrg.getOrgAlias())){
	  		  sqlBuf.append(" AND A.org_alias=? ");
	  		  qryList.add(imOrg.getOrgAlias());
	  	  }
    	
	  	sqlBuf.append(" ) WHERE ROWNUM<=? ORDER BY E.USER_ID asc) t2 WHERE T2.rn >=? ");
    	
        if (from < 0) {
            from = Integer.MAX_VALUE; 
        }
        
        qryList.add(size);
        qryList.add(from);
        Object[] param =qryList.toArray(); 
        List<ImUserPo> list = this.jdbcTemplate.query(sqlBuf.toString(),param,BeanPropertyRowMapper.newInstance(ImUserPo.class));
        return list;
    }
    
    
    
    
    @Override
    public void insertImOrg(ImOrganizationPo imOrg) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        sqlBuf.append(" insert into IM_ORGANIZATION ")                
              .append(" (org_id, area_id, org_code, org_name, parent_id, org_code_path, org_name_path, org_alias) " )
              .append(" VALUES (?,?,?,?,?,?,?,?) ");
        Object[] param = new Object[]{imOrg.getOrgId(),imOrg.getAreaId(),imOrg.getOrgCode(),imOrg.getOrgName(),
        		imOrg.getParentId(),imOrg.getOrgCodePath(),imOrg.getOrgNamePath(),imOrg.getOrgAlias()};
        this.jdbcTemplate.update(sqlBuf.toString(),param);
    }

	@Override
	public void delImOrg(ImOrganizationPo imOrg) throws Exception {
        String sqlStr = " delete from IM_ORGANIZATION where org_id=? ";
        Object[] param = new Object[]{imOrg.getOrgId()};
        this.jdbcTemplate.update(sqlStr,param);
    }

	@Override
	public void modifyImOrg(ImOrganizationPo imOrg) throws Exception {
		StringBuffer sqlBuf = new StringBuffer();
        sqlBuf.append(" update IM_ORGANIZATION set org_code=? , org_name=?, org_code_path=?, org_name_path=?, org_alias=? WHERE org_id=? ");   
        Object[] param = new Object[]{imOrg.getOrgCode(),imOrg.getOrgName(),imOrg.getOrgCodePath(),imOrg.getOrgNamePath(),imOrg.getOrgAlias()};
        this.jdbcTemplate.update(sqlBuf.toString(),param);
    }

	
}
